﻿/*
============================================================================
文件名    ： DBProviderForMSSQL.cs
完全限定名： SSS.DAL.DBProviderForMSSQL
作者      ： 陈存腾
创建时间  ： 2012-04-19
修改时间  ： 
版本      ：
说明      ： 数据库辅助类－基于ADO.NET对SQLServer进行基础封装
============================================================================
*/

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;


namespace SSS.DAL
{
    /// <summary>
    /// 数据库辅助类
    /// </summary>
    internal class DBProviderForMSSQL
    {
        #region 字段

        /// <summary>
        /// SQLServer数据库连接
        /// </summary>
        private static SqlConnection sqlCon = null;

        /// <summary>
        /// SQLServer数据库CMD
        /// </summary>
        private static SqlCommand sqlCmd = null;

        /// <summary>
        /// SQLServer数据库连接字符串
        /// </summary>
        private static string connectionString = null;

        /// <summary>
        /// 错误信息
        /// 正确执行的话，errorState值为null，否则为错误信息
        /// </summary>
        private static String errorState = null;

        /// <summary>
        /// SQLServer数据集
        /// </summary>
        private static SqlDataReader sqlDataReader = null;

        /// <summary>
        /// SqlCmd中存储过程的参数集合
        /// </summary>
        private static MyParameterCollection parameters = new MyParameterCollection();

        #endregion

        #region 属性

        /// <summary>
        /// 数据库连接
        /// </summary>
        internal static SqlConnection MyConnection
        {
            get { return DBProviderForMSSQL.sqlCon; }
        }

        /// <summary>
        /// 数据库CMD
        /// </summary>
        internal static SqlCommand MyCommand
        {
            get { return DBProviderForMSSQL.sqlCmd; }
        }

        /// <summary>
        /// 数据集
        /// </summary>
        public static SqlDataReader MyDataReader
        {
            get { return DBProviderForMSSQL.sqlDataReader; }
            set { DBProviderForMSSQL.sqlDataReader = value; }
        }

        /// <summary>
        /// SqlCmd中存储过程的参数集合
        /// </summary>
        internal static MyParameterCollection Parameters
        {
            get { return DBProviderForMSSQL.parameters; }
            set { DBProviderForMSSQL.parameters = value; }
        }

        /// <summary>
        /// 错误信息
        /// 正确执行的话，errorState值为null，否则为错误信息
        /// </summary>
        public static String ErrorState
        {
            get { return DBProviderForMSSQL.errorState; }
        }

        #endregion

        #region 子类型

        /// <summary>
        /// SqlCmd中存储过程的参数集合
        /// </summary>
        internal class MyParameterCollection
        {
            /// <summary>
            /// 重载的索引器 根据参数名查找存储过程参数的值
            /// </summary>
            /// <param name="name">参数名</param>
            /// <returns>目标存储过程参数的值</returns>
            public Object this[string name]
            {
                get
                {
                    // 传入参数(mysql的存储过程参数是以?打头,SQL Server以@开头)
                    name = String.Concat("@", name);
                    if (!DBProviderForMSSQL.sqlCmd.Parameters.Contains(name))
                    {
                        DBProviderForMSSQL.sqlCmd.Parameters.Add(new SqlParameter(name, SqlDbType.VarChar, 255));
                    }
                    return DBProviderForMSSQL.sqlCmd.Parameters[name].Value;
                }
                set
                {
                    name = String.Concat("@", name);
                    if (!DBProviderForMSSQL.sqlCmd.Parameters.Contains(name))
                    {
                        DBProviderForMSSQL.sqlCmd.Parameters.Add(new SqlParameter(name, SqlDbType.VarChar, 255));
                    }
                    DBProviderForMSSQL.sqlCmd.Parameters[name].Value = value;
                }
            }

            /// <summary>
            /// 重载的索引器 根据参数信息查找存储过程参数的值
            /// </summary>
            /// <param name="name">参数名</param>
            /// <param name="type">参数数据类型</param>
            /// <returns>目标存储过程参数的值</returns>
            public Object this[string name, SqlDbType type]
            {
                get
                {
                    name = String.Concat("@", name);
                    if (!DBProviderForMSSQL.sqlCmd.Parameters.Contains(name))
                    {
                        DBProviderForMSSQL.sqlCmd.Parameters.Add(new SqlParameter(name, type));
                    }
                    return DBProviderForMSSQL.sqlCmd.Parameters[name].Value;
                }
                set
                {
                    name = String.Concat("@", name);
                    if (!DBProviderForMSSQL.sqlCmd.Parameters.Contains(name))
                    {
                        DBProviderForMSSQL.sqlCmd.Parameters.Add(new SqlParameter(name, type));
                    }
                    DBProviderForMSSQL.sqlCmd.Parameters[name].Value = value;
                }
            }

            /// <summary>
            /// 重载的索引器 根据参数信息查找存储过程参数的值
            /// </summary>
            /// <param name="name">参数名</param>
            /// <param name="type">参数数据类型</param>
            /// <param name="size">参数大小</param>
            /// <returns>目标存储过程参数的值</returns>
            public Object this[string name, SqlDbType type, int size]
            {
                get
                {
                    // 传入参数(mysql的存储过程参数是以?打头,大多数数据库系统都是以@开头)
                    name = String.Concat("@", name);
                    if (!DBProviderForMSSQL.sqlCmd.Parameters.Contains(name))
                    {
                        DBProviderForMSSQL.sqlCmd.Parameters.Add(new SqlParameter(name, type, size));
                    }
                    return DBProviderForMSSQL.sqlCmd.Parameters[name].Value;
                }
                set
                {
                    name = String.Concat("@", name);
                    if (!DBProviderForMSSQL.sqlCmd.Parameters.Contains(name))
                    {
                        DBProviderForMSSQL.sqlCmd.Parameters.Add(new SqlParameter(name, type, size));
                    }
                    DBProviderForMSSQL.sqlCmd.Parameters[name].Value = value;
                }
            }

            /// <summary>
            /// 重载的索引器 根据参数信息查找存储过程参数的值
            /// </summary>
            /// <param name="name">参数名</param>
            /// <param name="type">参数数据类型</param>
            /// <param name="size">参数大小</param>
            /// <param name="direction">参数访问类型</param>
            /// <returns>目标存储过程参数的值</returns>
            public Object this[string name, SqlDbType type, int size, ParameterDirection direction]
            {
                get
                {
                    // 传入参数(mysql的存储过程参数是以?打头,大多数数据库系统都是以@开头)
                    name = String.Concat("@", name);
                    if (!DBProviderForMSSQL.sqlCmd.Parameters.Contains(name))
                    {
                        DBProviderForMSSQL.sqlCmd.Parameters.Add(new SqlParameter(name, type, size));
                        DBProviderForMSSQL.sqlCmd.Parameters[name].Direction = direction;
                    }
                    return DBProviderForMSSQL.sqlCmd.Parameters[name].Value;
                }
                set
                {
                    name = String.Concat("@", name);
                    if (!DBProviderForMSSQL.sqlCmd.Parameters.Contains(name))
                    {
                        DBProviderForMSSQL.sqlCmd.Parameters.Add(new SqlParameter(name, type, size));
                        DBProviderForMSSQL.sqlCmd.Parameters[name].Direction = direction;
                    }
                    DBProviderForMSSQL.sqlCmd.Parameters[name].Value = value;
                }
            }
        }

        #endregion

        #region 构造函数

        static DBProviderForMSSQL()
        {
            // 从网站Web.config中提取数据库连接字符串
            connectionString = SSS.Config.Configuration.ConnectionString;
            // 新建Sql数据库连接
            connect();
        }

        #endregion

        #region 私有方法

        #region 新建Sql数据库连接

        /// <summary>
        /// 新建Sql数据库连接
        /// </summary>
        /// <returns>操作是否成功</returns>
        private static bool connect()
        {
            try
            {
                // 新建Sql数据库连接及CMD对象
                sqlCon = new SqlConnection(connectionString);
                sqlCmd = sqlCon.CreateCommand();
                // 设置调用方式为存储过程
                sqlCmd.CommandType = CommandType.StoredProcedure;
                return true;
            }
            catch (System.Exception ex)
            {
                errorState = ex.Message;
                return false;
            }
        }

        #endregion

        #endregion

        #region 公共方法

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <returns>是否成功打开数据库连接</returns>
        public static bool Open()
        {
            if ((sqlCon.State == ConnectionState.Broken) || (sqlCon.State == ConnectionState.Closed))
            {
                sqlCon.Open();
                return true;
            }
            return false;
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public static void Close()
        {
            if (sqlCon.State != ConnectionState.Closed)
            {
                sqlCon.Close();
            }
        }

        #region 执行

        /// <summary>
        /// 执行增删改等存储过程
        /// </summary>
        /// <returns>记录变更的行数</returns>
        internal static int ExecuteNonQuery()
        {
            try
            {
                return sqlCmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 执行增删改等存储过程
        /// </summary>
        /// <param name="sql">待执行的sql命令</param>
        /// <returns>记录变更的行数</returns>
        internal static int ExecuteNonQuery(string sql)
        {
            sqlCmd.CommandText = sql;
            return ExecuteNonQuery();
        }

        /// <summary>
        /// 执行查询，并返回结果集中第一行第一列
        /// </summary>
        /// <returns>结果集中第一行第一列</returns>
        internal static object ExecuteScalar()
        {
            try
            {
                return sqlCmd.ExecuteScalar();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 执行查询，并返回结果集中第一行第一列
        /// </summary>
        /// <param name="sql">待执行的sql命令</param>
        /// <returns>结果集中第一行第一列</returns>
        internal static object ExecuteScalar(string sql)
        {
            sqlCmd.CommandText = sql;
            return ExecuteScalar();
        }

        /// <summary>
        /// 执行查询SQL语句或存储过程
        /// </summary>
        /// <returns>查询结构</returns>
        internal static DataTable ExecuteReader()
        {
            DataTable dataTable = new DataTable();
            using (sqlDataReader = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dataTable.Load(sqlDataReader);
            }
            return dataTable;
        }

        /// <summary>
        /// 执行查询SQL语句或存储过程
        /// </summary>
        /// <param name="sql">待执行的sql命令</param>
        /// <returns>查询结构</returns>
        internal static DataTable ExecuteReader(string sql)
        {
            sqlCmd.CommandText = sql;
            return ExecuteReader();
        }

        #endregion

        #endregion


        public static System.Data.DataTable test()
        {
            SqlDataReader sdr = null;
            DataTable dt = new DataTable();
            try
            {
                if (!sqlCmd.Parameters.Contains("@p_no"))
                {
                    sqlCmd.Parameters.Add(new SqlParameter("@p_no", SqlDbType.Int));
                }
                sqlCmd.Parameters["@p_no"].Value = 1;


                sqlCmd.CommandText = "test";
                sqlCon.Open();
                using (sdr = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    dt.Load(sdr);
                }
            }
            catch (System.Exception ex)
            {
                errorState = ex.Message;
            }
            sqlCmd.Parameters.Add(new SqlParameter("@p_no", SqlDbType.Int));
            return dt;
        }

    }
}